/************************************************************************
 PROJECT     : Phoenix iTill System Database Creation Scripts
 FILE        : CreateTables.sql
 REVISION    : 1.0
 FUNCTION    : Creation script for Tables
 AUTHOR      : Helen Volosuk <lilo@axmor.com> 

 COMMENTS    : WARNING! The correct order of script application is:
               ClearDatabase.sql
               CreateTables.sql
               CreateProcs.sql *

**************************************************************************
 CHANGE HISTORY:
 Revision      Date       Author
 -------------------------------------------------------------------------
 Revision 1.0  2005/09/02 lilo 
 Initial draft

**************************************************************************/

SET XACT_ABORT ON
SET NOCOUNT ON
BEGIN TRANSACTION

CREATE TABLE MasterLevel
(
	MasterLevelID INT IDENTITY(1,1) NOT NULL,
	LevelName NVARCHAR(100) NULL,
	Deleted BIT NOT NULL DEFAULT(0),
	CONSTRAINT PK_MasterLevel PRIMARY KEY CLUSTERED (MasterLevelID) 
)
GO




CREATE TABLE Schedule
(
	ScheduleID INT IDENTITY(1,1) NOT NULL,
	StartTime INT NOT NULL,
	FinishTime INT NOT NULL,
	Busy BIT NOT NULL DEFAULT(0),
	Period TINYINT NULL,
	DayOfWeek TINYINT NULL,
	DayOfMonth INT NULL,
	ExactDate DATETIME NULL,
	Descript NVARCHAR(512) NULL,
	EmployeeID INT NULL,
	CONSTRAINT PK_Scedule PRIMARY KEY CLUSTERED (ScheduleID) 
)
GO

CREATE TABLE Client
(
	ClientID INT IDENTITY(1,1) NOT NULL,
	FullName NVARCHAR(128) NOT NULL,
	Gender CHAR(1) NOT NULL,
	InOffice TINYINT NOT NULL DEFAULT(0),
	Comment NTEXT NULL,
	Address NVARCHAR(512) NULL,
	Phone NVARCHAR(128) NULL,
	BirthDate NVARCHAR(128) NULL,
	Email NVARCHAR (512) NULL,
	Deleted BIT NOT NULL DEFAULT(0),
	CONSTRAINT PK_Client PRIMARY KEY  CLUSTERED (ClientID),
	CONSTRAINT CK_Client_Gender CHECK (Gender='M' or Gender='F')
)
GO


CREATE TABLE Product
(
	ProductID INT IDENTITY(1,1) NOT NULL,
	Label NVARCHAR(128) Not NULL DEFAULT('New Product'),
	Price MONEY NULL DEFAULT(0),
	Type NVARCHAR(128) NULL DEFAULT (''),
	Family NVARCHAR(128) NULL DEFAULT(''),
	Stock INT NULL DEFAULT(0),
	CriticalStock INT NULL DEFAULT(0),
	Comment NTEXT NULL,
	Deleted BIT NULL DEFAULT(0),
	CONSTRAINT PK_Product PRIMARY KEY  CLUSTERED (ProductID)  
)
GO

CREATE TABLE Service
(
	ServiceID INT IDENTITY(1,1) NOT NULL,
	Label NVARCHAR(128) NOT NULL,
	Type NVARCHAR(128) NULL default(''),
	Comment NTEXT NULL,
	Color INT NULL,
	TextColor INT NULL,
	Deleted BIT NOT NULL DEFAULT(0),
	CONSTRAINT PK_Service PRIMARY KEY  CLUSTERED (ServiceID)	
)
GO

CREATE TABLE ServiceParam
(
	ServiceParamID INT IDENTITY(1,1) NOT NULL,
	ServiceID INT NOT NULL,
	MasterLevelID INT NOT NULL,
	Price MONEY NOT NULL,
	Duration INT NOT NULL,
	CONSTRAINT PK_ServiceParam PRIMARY KEY  CLUSTERED (ServiceParamID),
	CONSTRAINT FK_ServiceParam_Service FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID),
	CONSTRAINT FK_ServiceParam_MasterLevel FOREIGN KEY (MasterLevelID) REFERENCES MasterLevel(MasterLevelID)
)
GO


CREATE TABLE Employee
(
	EmployeeID INT IDENTITY(1,1) NOT NULL,
	FullName NVARCHAR(128) NOT NULL,
	Position NVARCHAR(128) NULL DEFAULT(''),
	MasterLevelID INT NOT NULL,
	SecurityLevel INT NOT NULL DEFAULT(0),
	Phone NVARCHAR(128) NULL,
	Address NVARCHAR(512) NULL,
	Comments NVARCHAR(2048) NULL,
	Password NVARCHAR(64) NULL,
	Sys BIT NOT NULL DEFAULT(0),
	IsOperator BIT NOT NULL DEFAULT(1),
	IsAttendant BIT NOT NULL DEFAULT(1),
	Photo IMAGE NULL,
	Deleted BIT NOT NULL DEFAULT(0),
	CONSTRAINT FK_Employee_MasterLevel FOREIGN KEY (MasterLevelID) REFERENCES MasterLevel(MasterLevelID),
	CONSTRAINT PK_Employee PRIMARY KEY  CLUSTERED (EmployeeID)
)
GO

CREATE TABLE BillNoSale
(
	BillNoSaleID INT IDENTITY(1,1) NOT NULL,
	PaymentSum FLOAT NOT NULL DEFAULT(0),
	Comment NVARCHAR(2048) NULL,
	EmployeeID INT NOT NULL,
	CreationTime DATETIME NOT NULL,
	OperatorID INT NOT NULL
	CONSTRAINT FK_BillNoSale_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
	CONSTRAINT FK_BillNoSale_OperatorID FOREIGN KEY (OperatorID) REFERENCES Employee(EmployeeID),
	CONSTRAINT PK_BillNoSale PRIMARY KEY CLUSTERED (BillNoSaleID) 
)
GO


CREATE TABLE Bill 
(
	BillID INT IDENTITY(1,1) NOT NULL,
	PaymentSumm FLOAT NOT NULL DEFAULT(0),
	ClientID INT NULL,
	OperatorID INT NULL,
	CreationTime DATETIME NULL DEFAULT (GETDATE()),
	CONSTRAINT FK_Bill_Client FOREIGN KEY (ClientID) REFERENCES Client(ClientID),
	CONSTRAINT FK_Bill_Employee FOREIGN KEY (OperatorID) REFERENCES Employee(EmployeeID),
	CONSTRAINT PK_Bill PRIMARY KEY CLUSTERED (BillID) 
)
GO

CREATE TABLE Payment
(
	PaymentID INT IDENTITY(1,1) NOT NULL,
	PaymentType TINYINT NOT NULL,
	CardName NVARCHAR(128) NULL,
	PaymentSumm FLOAT NOT NULL,
	BillID INT NOT NULL,
	CONSTRAINT PK_Payment PRIMARY KEY  CLUSTERED (PaymentID),
	CONSTRAINT FK_Payment_Bill FOREIGN KEY (BillID) REFERENCES Bill(BillID) 
)
GO

CREATE TABLE Appointment
(
	AppointmentID INT IDENTITY(1,1) NOT NULL,
	ClientID INT NULL ,
	PerformerID INT NULL ,
	OperatorID INT NULL DEFAULT(1),
	StartTime DATETIME NULL DEFAULT(GETDATE()),
	FinishTime DATETIME NULL DEFAULT(GETDATE()),
	CreationTime DATETIME NULL DEFAULT (GETDATE()),
	ExpectedDuration INT NULL DEFAULT(0),
	Comment NTEXT NULL ,
	BillID INT NULL DEFAULT(-1),
	ServiceID int NULL ,
	ServiceCost MONEY NULL DEFAULT(0),
	Description NVARCHAR(512) NULL,
	Deleted BIT NULL DEFAULT(0),
	CONSTRAINT PK_Appointment PRIMARY KEY  CLUSTERED (AppointmentID),
	CONSTRAINT FK_Appointment_Client FOREIGN KEY (ClientID) REFERENCES Client(ClientID),
	CONSTRAINT FK_Appointment_Performer FOREIGN KEY (PerformerID) REFERENCES Employee(EmployeeID),
	CONSTRAINT FK_Appointment_Service FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID)
)
GO



CREATE TABLE BillProduct
(
	BillProductID INT IDENTITY(1,1) NOT NULL,
	ProductID INT NOT NULL,
	BillID INT NOT NULL,
	Cost FLOAT NOT NULL,
	CONSTRAINT PK_BillProduct PRIMARY KEY  CLUSTERED (BillProductID),
	CONSTRAINT FK_BillProduct_Bill FOREIGN KEY (BillID) REFERENCES Bill(BillID),
	CONSTRAINT FK_BillProduct_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
)
GO



-- views

CREATE VIEW [dbo].[PaymentView]
AS
SELECT     PaymentID, PaymentType, 
			CASE PaymentType WHEN 2 THEN CardName ELSE NULL END AS CardName, 
			PaymentSumm, BillID
FROM         dbo.Payment
GO

--index creation



CREATE  INDEX IX_Payment_BillID ON Payment(BillID)
GO

CREATE  INDEX IX_Appointment_ClientID ON Appointment(ClientID)
GO

CREATE  INDEX IX_Appointment_RerformerID ON Appointment(PerformerID)
GO

CREATE  INDEX IX_Appointment_OperatorID ON Appointment(OperatorID)
GO

CREATE  INDEX IX_Appointment_BillID ON Appointment(BillID)
GO

CREATE  INDEX IX_BillProduct_ProductID ON BillProduct(ProductID)
GO

CREATE  INDEX IX_BillProduct_AppointmentID ON BillProduct(BillID)
GO

/*
insert into Client (FullName, InOffice, Gender) values ('John Smith', 0, 'M')
insert into Client (FullName, InOffice, Gender) values ('John Johnes', 0, 'M')
insert into Client (FullName, InOffice, Gender) values ('Ronald Sun', 0, 'M')
insert into Client (FullName, InOffice, Gender) values ('Alex Jazz', 0, 'M')
insert into Client (FullName, InOffice, Gender) values ('Alan Gold', 0, 'M')
insert into Client (FullName, InOffice, Gender) values ('Richard Grey', 0, 'M')

insert into Service (Label, Type) values ('Wedding haircut', 'Cut')
insert into Service (Label, Type) values ('Extra Syling', 'Styling')
insert into Service (Label, Type) values ('Complex haircut', 'Cut')
insert into Service (Label, Type) values ('New Styling', 'Styling')
insert into Service (Label, Type) values ('Sunburn', 'Sunbed')
insert into Service (Label, Type) values ('Blond', 'Highlights')

insert into Product (Label, Type, Family, Price, Stock, CriticalStock) values ('Wella Vosene', 'shampoo','wella',20,30,2)
insert into Product (Label, Type, Family, Price, Stock, CriticalStock) values ('Curl Boosting Spray', 'hair spray','wella',20,30,2)
insert into Product (Label, Type, Family, Price, Stock, CriticalStock) values ('Strong Control Styling Spray', 'hair spray','wella',20,30,2)
insert into Product (Label, Type, Family, Price, Stock, CriticalStock) values ('Wella Vivality', 'shampoo','wella',20,30,2)
insert into Product (Label, Type, Family, Price, Stock, CriticalStock) values ('Ceramide resist', 'nail varnish','loreal',20,30,2)
insert into Product (Label, Type, Family, Price, Stock, CriticalStock) values ('Jet set shine', 'nail varnish','loreal',20,30,2)
*/

insert into Schedule (StartTime, FinishTime, DayOfWeek) values (540, 1020, 1)
insert into Schedule (StartTime, FinishTime, DayOfWeek) values (540, 1020, 2)
insert into Schedule (StartTime, FinishTime, DayOfWeek) values (540, 1020, 3)
insert into Schedule (StartTime, FinishTime, DayOfWeek) values (540, 1020, 4)
insert into Schedule (StartTime, FinishTime, DayOfWeek) values (540, 1020, 5)


insert into MasterLevel (LevelName) values ('Beginner')
insert into MasterLevel (LevelName) values ('Master')

INSERT INTO Employee (FullName, SecurityLevel, Sys, MasterLevelID, IsAttendant) VALUES('Salon Adminitsrator', 2, 1, 1, 0)
INSERT INTO Employee (FullName, SecurityLevel, Sys, MasterLevelID, IsAttendant) VALUES('Salon Manager', 1, 1,2,0)
INSERT INTO Employee (FullName, SecurityLevel, Sys, MasterLevelID, IsAttendant) VALUES('Salon User', 0, 1,2,0)


/*
insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (1, 1, 20, 120)
insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (1, 2, 30, 60)

insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (2, 1, 40, 120)
insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (2, 2, 50, 60)

insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (3, 1, 60, 120)
insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (3, 2, 70, 60)

insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (4, 1, 80, 120)
insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (4, 2, 90, 60)

insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (5, 1, 100, 120)
insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (5, 2, 110, 60)

insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (6, 1, 120, 120)
insert into ServiceParam (ServiceID, MasterLevelID, Price, Duration) values (6, 2, 130, 60)
*/

-- Create Procedures




COMMIT TRANSACTION

